/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package persistense;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import model.Conta;
import state.ContaEstado;
import strategy.OperacaoExtrato;

/**
 *
 * @author Aurelio
 */
public class ContaDAO {
    private static ContaDAO instance = new ContaDAO();
    
    private ContaDAO(){
    }
    
    public static ContaDAO getInstance(){
        return instance;
    }
    
    public void salvar(Conta conta)throws SQLException, ClassNotFoundException{
        Connection conn = null;
        PreparedStatement pst = null;

        try {
              conn = DataBaseLocator.getInsLocator().getConnection();
              pst = conn.prepareStatement("insert into conta (numero, digito, saldo, id_titular) values(?,?,?,?)");
              pst.setString(1, conta.getNumero());
              pst.setString(2, conta.getDigito());
              pst.setDouble(3, conta.getSaldo());
              pst.setInt(4, conta.getTitular().getId());
              pst.executeUpdate();
        } catch(SQLException e) {
                throw e;
        } finally {
                closeResources(conn, pst);
        }
    }
    
    public Conta recuperarContaporNumero(String numero){
        Connection conn = null;
        PreparedStatement pst = null;    
        ResultSet rs = null;
        Conta conta = new Conta();
        try {
              conn = DataBaseLocator.getInsLocator().getConnection();
              pst = conn.prepareStatement("select * from conta where numero=?");
              pst.setString(1, numero);
              rs = pst.executeQuery();
              while(rs.next()){
                  
                  conta.setId(rs.getInt("id"));
                  conta.setNumero(rs.getString("numero"));
                  conta.setDigito(rs.getString("digito"));
                  conta.setSaldo(rs.getDouble("saldo"));
                  conta.setContaEstado(new ContaEstado());
                  conta.getContaEstado().setIdEstado(rs.getInt("id_estado"));
              }
                         
            } catch (Exception e) {
                e.printStackTrace();
            }
        return conta;
    }
    
    public void closeResources(Connection conn, Statement st){
      try {
            if(st!=null) st.close();
            if(conn!=null) conn.close();
          } catch(SQLException e) {
                    
          }
    }

    public List<OperacaoExtrato> recuperaHistorico(String numero_conta) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            List<OperacaoExtrato> historico = new ArrayList<OperacaoExtrato>();
            conn = DataBaseLocator.getInsLocator().getConnection();
            pst = conn.prepareStatement("SELECT * FROM operacao op JOIN conta c ON c.id=op.id_conta"
                    + " WHERE c.numero="+numero_conta);
            rs = pst.executeQuery();
            
            while(rs.next()){
                OperacaoExtrato op = new OperacaoExtrato();
                op.setValor(rs.getDouble("valor"));
                op.setData(rs.getDate("data"));
                historico.add(op);
            }
            return historico;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        
        }finally{
            closeResources(conn, pst);
        }
    }   
    
        public ContaEstado recuperarEstado(String numero){
        Connection conn = null;
        PreparedStatement pst = null;    
        ResultSet rs = null;
        ContaEstado conta = null;
        Class classe = null;
        Object object = null;
        try {
              conn = DataBaseLocator.getInsLocator().getConnection();
              pst = conn.prepareStatement("select e.descricao from conta c, estado e "
                      + "where e.idestado=c.id_estado and numero=?");
              pst.setString(1, numero);
              rs = pst.executeQuery();
              while(rs.next()){
                   String nomeClasse = "state.";
                   nomeClasse += rs.getString(1);
                   classe = Class.forName(nomeClasse);
                   object = classe.newInstance();
                   conta = (ContaEstado) object;
                   
              }
                         
            } catch (Exception e) {
                e.printStackTrace();
            }
        return conta;
    }
}
